========================================================================= 
Log Path: /Users/wenhao/Dropbox/Research/Inverted Liquidity Crises/Data/Firm quality and financial availability - Wenhao's version/output/log/log_CRSPmonthly.log 
Program Path: /Users/wenhao/Dropbox/Research/Inverted Liquidity Crises/Data/Firm quality and financial availability - Wenhao's version/code/0_DownloadData/4_CRSP_monthly.R 
Working Directory: /Users/wenhao/Dropbox/Research/Inverted Liquidity Crises/Data/Firm quality and financial availability - Wenhao's version 
User Name: wenhao 
R Version: 4.4.0 (2024-04-24) 
Machine: Wenhaos-MacBook-2020.local x86_64 
Operating System: Darwin 23.4.0 Darwin Kernel Version 23.4.0: Wed Feb 21 21:44:31 PST 2024; root:xnu-10063.101.15~2/RELEASE_X86_64 
Base Packages: stats graphics grDevices utils datasets methods base 
Other Packages: tidylog_1.1.0 zoo_1.8-12 dtplyr_1.3.1 RPostgres_1.4.7 procs_1.0.6 reporter_1.4.4 libr_1.3.3 logr_1.3.8 fmtr_1.6.5 common_1.1.3 sassy_1.2.5 here_1.0.1 data.table_1.15.4 lubridate_1.9.3 forcats_1.0.0 stringr_1.5.1 dplyr_1.1.4 purrr_1.0.2 readr_2.1.5 tidyr_1.3.1 tibble_3.2.1 ggplot2_3.5.1 tidyverse_2.0.0 
Log Start Time: 2024-07-08 11:12:53.15821 
========================================================================= 

> library(tidyverse)
> library(data.table)
> library(dtplyr)
> library(lubridate)
> library(stringr)
> library(zoo)
> library(here)
> library(sassy)
> library(RPostgres)
> 
> # Open the log
> lf <- log_open(file.path(here("output", "log"), "log_CRSPmonthly.log"),
>                autolog = T, show_notes = F)
> 
> 
> # Send code to the log
> log_code()
> 
> # 1 Read data from WRDS -------------------------------------------------------------------
> sep("1 Read data from WRDS")
> 
> wrds <- dbConnect(Postgres(),
>                   host='wrds-pgdata.wharton.upenn.edu',
>                   port=9737,
>                   dbname='wrds',
>                   sslmode='require',
>                   user='wenhao19')
> 
> 
> SQL_statement <- 
>   "SELECT a.permno, a.permco, a.date, a.ret, a.retx, a.vol,
>           a.shrout, a.prc, a.cfacshr, a.bidlo, a.askhi,
>           b.shrcd, b.exchcd, b.siccd, b.ticker, b.shrcls, 
>           c.dlstcd, c.dlret                               
>    FROM crsp.msf as a
>    LEFT JOIN crsp.msenames as b
>     ON a.permno=b.permno AND b.namedt<=a.date AND a.date<=b.nameendt
>    LEFT JOIN crsp.msedelist as c
>     ON a.permno=c.permno AND date_trunc('month', a.date) = date_trunc('month', c.dlstdt)"
> 
> res <- dbSendQuery(conn = wrds, statement = SQL_statement)
> df_monthlyCRSP <- dbFetch(res)
> dbClearResult(res)
> 
> # 2 Cleaning -------------------------------------------------------------------
> sep("2 Cleaning")
> 
> # Make 2 digit SIC
> df_monthlyCRSP <- df_monthlyCRSP %>%
>   rename(sicCRSP = siccd) %>%
>   mutate(sicCRSP = as.character(sicCRSP),
>          sic2D = substr(sicCRSP, 1, 2),
>          sic2D = as.numeric(sic2D))
> 
> # Create monthly date
> df_monthlyCRSP <- df_monthlyCRSP %>%
>   mutate(time_avail_m = floor_date(as.Date(date, origin = "1960-01-01"), "month")) %>%
>   select(-date)
> 
> # Incorporate delisting return
> df_monthlyCRSP <- df_monthlyCRSP %>%
>   mutate(dlret = case_when(
>     is.na(dlret) & (dlstcd == 500 | (dlstcd >= 520 & dlstcd <= 584)) & (exchcd == 1 | exchcd == 2) ~ -0.35,
>     is.na(dlret) & (dlstcd == 500 | (dlstcd >= 520 & dlstcd <= 584)) & exchcd == 3 ~ -0.55,
>     dlret < -1 & !is.na(dlret) ~ -1,
>     is.na(dlret) ~ 0,
>     TRUE ~ dlret
>   ))
> 
> # Update return values
> df_monthlyCRSP <- df_monthlyCRSP %>%
>   mutate(ret = if_else(!is.na(ret), (1 + ret) * (1 + dlret) - 1, dlret),
>          ret = if_else(is.na(ret) & dlret != 0, dlret, ret))
> 
> # Compute market value of equity
> df_monthlyCRSP <- df_monthlyCRSP %>%
>   mutate(shrout = shrout / 1000,
>          vol = vol / 10^4,
>          mve_c = shrout * abs(prc))
> 
> # Housekeeping
> df_monthlyCRSP <- df_monthlyCRSP %>%
>   select(-dlret, -dlstcd, -permco) 
> 
> # save
> fwrite(df_monthlyCRSP, here("data", "CRSP", "monthlyCRSP.csv"))
> saveRDS(df_monthlyCRSP, here("data", "CRSP", "monthlyCRSP.RDS"))
> 
> # Close log
> log_close()
> 
> # View results
> writeLines(readLines(lf))

========================================================================= 
1 Read data from WRDS 
========================================================================= 

========================================================================= 
2 Cleaning 
========================================================================= 

rename: renamed one variable (sicCRSP)

mutate: converted 'sicCRSP' from integer to character (0 new NA)

        new variable 'sic2D' (double) with 88 unique values and 1% NA

mutate: new variable 'time_avail_m' (Date) with 1,177 unique values and 0% NA

select: dropped one variable (date)

mutate: changed 5,009,203 values (99%) of 'dlret' (5,009,203 fewer NAs)

mutate: changed 4,294,533 values (85%) of 'ret' (187,183 fewer NAs)

mutate: changed 4,601,857 values (91%) of 'vol' (0 new NAs)

        changed 4,998,874 values (99%) of 'shrout' (0 new NAs)

        new variable 'mve_c' (double) with 3,010,198 unique values and 3% NA

select: dropped 3 variables (permco, dlstcd, dlret)

========================================================================= 
Log End Time: 2024-07-08 11:15:03.88375 
Log Elapsed Time: 0 00:02:10 
========================================================================= 
